package edu.ncsu.csc.itrust.dao.mysql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import edu.ncsu.csc.itrust.DBUtil;
import edu.ncsu.csc.itrust.beans.DiagnosisBean;
import edu.ncsu.csc.itrust.beans.OfficeVisitBean;
import edu.ncsu.csc.itrust.beans.loaders.DiagnosisBeanLoader;
import edu.ncsu.csc.itrust.dao.DAOFactory;
import edu.ncsu.csc.itrust.exception.DBException;

/**
 * DAO class for adding Diagnoses to the database for Office Visits
 * @author Patrick Finegan & Bailey Hayes
 *
 */
public class OfficeVisitDiagnosisDAO {

	/** Obligatory object for adding to Mysql database */
	private DAOFactory factory;
	
	/** Obligatory loader for diagnosis bean*/
	private DiagnosisBeanLoader diagnosisLoader = new DiagnosisBeanLoader(true);
	
	/**
	 * The typical constructor.
	 * @param factory The {@link DAOFactory} associated with this DAO, which is used for obtaining SQL connections, etc.
	 */
	public OfficeVisitDiagnosisDAO(DAOFactory factory) {
		this.factory = factory;
	}
	
	/**
	 * Returns all of the diagnoses associated with the given office visit
	 * 
	 * @param visitID The unique ID of the office visit.
	 * @return A java.util.List of diagnoses.
	 * @throws DBException
	 */
	public List<DiagnosisBean> getDiagnoses(long visitID) throws DBException {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = factory.getConnection();
			ps = conn.prepareStatement("Select * From OVDiagnosis,ICDCodes Where OVDiagnosis.VisitID = ? "
					+ "AND ICDCodes.Code=OVDiagnosis.ICDCode");
			ps.setLong(1, visitID);
			ResultSet rs = ps.executeQuery();
			return diagnosisLoader.loadList(rs);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DBException(e);
		} finally {
			DBUtil.closeConnection(conn, ps);
		}
	}
	
	/**
	 * Adds the given CPT codes to the given office visit
	 * @param icd A double representing the ICD code to be added.
	 * @param visitID The ID of the office visit we are adding the code to.
	 * @return A long for the new ICD code's ID.
	 * @throws DBException
	 */
	public long addDiagnosisToOfficeVisit(double icd, long visitID) throws DBException {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = factory.getConnection();
			ps = conn.prepareStatement("INSERT INTO OVDiagnosis (ICDCode,VisitID) VALUES (?,?)");
			ps.setDouble(1, icd);
			ps.setLong(2, visitID);
			ps.executeUpdate();
			return DBUtil.getLastInsert(conn);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DBException(e);
		} finally {
			DBUtil.closeConnection(conn, ps);
		}
	}
	
	/**
	 * Removes a particular diagnosis from its office visit
	 * 
	 * @param ovDiagnosisID The ID for the office visit diagnosis to be removed. 
	 * @throws DBException
	 */
	public void removeDiagnosisFromOfficeVisit(long ovDiagnosisID) throws DBException {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = factory.getConnection();
			ps = conn.prepareStatement("DELETE FROM OVDiagnosis WHERE ID=? ");
			ps.setLong(1, ovDiagnosisID);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DBException(e);
		} finally {
			DBUtil.closeConnection(conn, ps);
		}
	}
	
	/**
	 * Gets all office visits corresponding to a particular ICD diagnosis.
	 * 
	 * @param icdcode A string represnting the ICD diagnosis to look up.
	 * @return A java.util.List of Office visits.
	 * @throws DBException
	 */
	public List<OfficeVisitBean> getAllOfficeVisitsForDiagnosis(String icdcode) throws DBException {
		
		OfficeVisitDAO temp = factory.getOfficeVisitDAO();
		
		List<DiagnosisBean> diags = null;
		List<OfficeVisitBean> ovs = new ArrayList<OfficeVisitBean>();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs= null;
		
		try {
			if (icdcode == null) 
				throw new SQLException("icdcode cannot be null");
			
			conn = factory.getConnection();
			
			ps = conn.prepareStatement("SELECT * FROM ovdiagnosis ovd, icdcodes icd WHERE ovd.ICDCode=? and icd.Code=?");
			ps.setString(1, icdcode);
			ps.setString(2, icdcode);
			rs = ps.executeQuery();
			diags = diagnosisLoader.loadList(rs);
			rs.close();
			ps.close();
			ps = null;
			rs = null;
			
			for (DiagnosisBean bean: diags) {
				ps = conn.prepareStatement("SELECT * FROM officevisits ov WHERE ov.ID=?");
				ps.setInt(1, (int)bean.getVisitID());
				rs = ps.executeQuery();
				
				if (rs.next()) {
					ovs.add(temp.loadFullOfficeVist(rs, bean.getVisitID()));
				}
				rs.close();
				ps.close();
			}
			
			return ovs;
		
		}
		catch (SQLException e) {
			e.printStackTrace();
			throw new DBException(e);
		} finally {
			DBUtil.closeConnection(conn, ps);
		}
	}
	
	/**
	 * Edits an existing diagnosis bean.
	 * 
	 * @param diag The newly updated diagnosis bean.
	 * @return A long indicating the ID of the newly updated diagnosis bean.
	 * @throws DBException
	 */
	public long editDiagnosis(DiagnosisBean diag) throws DBException {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = factory.getConnection();
			String statement = "UPDATE OVDiagnosis " +
				"SET VisitID=?, ICDCode=? WHERE ID=?";
			ps = conn.prepareStatement(statement);
			ps.setLong(1, diag.getVisitID());
			ps.setString(2, diag.getICDCode());
			ps.setLong(3, diag.getOvDiagnosisID());
			ps.executeUpdate();
			return DBUtil.getLastInsert(conn);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DBException(e);
		} finally {
			DBUtil.closeConnection(conn, ps);
		}
	}
}
